import pandas as pd
import json
# -*- coding: utf-8 -*-
import os
import sys
import redis
import xpinyin

r = redis.Redis(host='localhost', port=6379, decode_responses=True)

pwd = os.path.dirname(os.path.dirname(__file__))

# 进入django设置
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'shop.settings')
import requests
import time
import django

django.setup()


def get_s(con):
    p = xpinyin.Pinyin()
    result = [p.get_initials(a, '') for a in [con]][0]
    return result


from goods.models import Good, GoodCategory,  GoodBrand, GoodTags, Source





def read_category2():
    # cate1
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate1 = row['cate2']
            cate1_zh = row['cate2中文']
            cate1_py = get_s(cate1_zh.replace('/', ''))

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)

            if s1.first():
                pass
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 2
                category1.parent_category = GoodCategory.objects.get(modify_name='宠物用品')
                category1.save()


        except Exception as e:
            with open('pet1.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def read_category3():
    # cate1
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate = row['cate2']
            cate1 = row['cate3']
            cate1_zh = row['cate3中文']
            cate1_py = get_s(cate1_zh.replace('/', ''))

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)

            if s1.first():
                pass
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 3
                category1.parent_category = GoodCategory.objects.get(name=cate)
                category1.save()


        except Exception as e:
            with open('pet.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def brand():
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            jp = '无品牌' if row['jp_brand'] == '' else row['jp_brand']

            b1 = GoodBrand.objects.filter(jp_name=jp).first()
            if b1:
                pass
            else:
                brand = GoodBrand()
                brand.category = GoodCategory.objects.get(name='宠物用品')
                brand.jp_name = jp
                brand.en_name = ''
                brand.save()
        except:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good():
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            cate = row['cate3']
            en = row['jp_brand']
            sn = row['pid']
            title_x = row['title']
            sku = eval(row['skus'])[0]
            good = Good()
            good.category = GoodCategory.objects.get(name=cate)
            good.source = Source.objects.get(name='petpochitto')
            good.brand = GoodBrand.objects.get(jp_name=en)
            good.sn = sn
            good.title = title_x
            good.desc = sku['description']
            good.detail = sku['content']
            good.save()


        except Exception as e:
            print(e)
            with open('pet.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


from hashlib import md5


def request_params_md5(p):
    m = md5()
    m.update(str(p).encode('utf-8'))
    return m.hexdigest()


def good_param():
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            pid = row['pid']
            sku = eval(row['skus'])
            url = row['url']
            kk = Good.objects.get(sn=pid)
            for sk in sku:
                print(sk)
                param = GoodParam()
                param.be_good = kk
                param.classify = row['title']
                param.sku_id = request_params_md5(sk['sku'] + url)
                param.good_model = ''
                param.status = 1 if sk['status'] == 'InStock' else 0
                param.cover = sk['image'][0]
                # param.shop_price = sk['sold_price']
                param.market_price = sk['market_price']
                param.save()


        except Exception as e:
            print(e)
            with open('pet.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good_image():
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            pid = row['pid']
            sku = eval(row['skus'])
            kk = Good.objects.get(sn=pid)
            # print(sku)
            for goods_image in sku:
                imgs = goods_image['image']
                for img in imgs:
                    goods_image_instance = GoodCarouselImage()
                    goods_image_instance.image = img
                    goods_image_instance.good = kk
                    goods_image_instance.save()

        except Exception as e:
            print(e)
            with open('pet.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def last_category3():
    # cate1
    df = pd.read_excel(r'petpochitto中文(1).xls')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate = '鸟用品'
            cate1 = row['cate3']
            cate1_zh = row['cate3中文']
            cate1_py = get_s(cate1_zh.replace('/', ''))

            # 一级
            s1 = GoodCategory.objects.filter(name=cate1)

            if s1.first():
                pass
            else:
                category1.name = cate1
                category1.modify_name = cate1_zh
                category1.code = cate1_py
                category1.category_type = 3
                category1.parent_category = GoodCategory.objects.get(name=cate)
                category1.save()


        except Exception as e:
            print(e)
            with open('pet.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


if __name__ == '__main__':
    read_category3()
    # read_category3()
    # brand()
    # good_param()
    # good_image()
    # last_category3()
